﻿using System;
using System.Data;
using System.Data.SQLite;
using System.Text;
using NDK.Utils;
using NDK.Module.Model;

namespace NDK.Module.DAL
{
    /// <summary>
    /// 操作指纹信息表
    /// </summary>
    class UserFPInfoServices
    {

        //查询数据表中所有行的数据
        public DataSet SelectTotalInfo(string tableName)
        {
            StringBuilder strSql = new StringBuilder();  //SELECT COUNT(*) FROM m_table
            strSql.Append("select  *");
            strSql.Append(" FROM " + tableName);

            return SQLiteUtils.Query(strSql.ToString());
        }


        //查询指定值是否存在于指定的字段中
        public int InquirySpecifyInfo2Table(string TypeName, string Typevalue, string tableName)
        {
            SQLiteConnection m_Conn = SQLiteUtils.GetConnection(Common.DatabaseName);
            string sql = "select count(*) FROM " + tableName + " where " + TypeName + " = " + "'" + Typevalue + "'";
            SQLiteCommand cmdCreateTable = new SQLiteCommand(sql, m_Conn);
            int count = (int)(Int64)cmdCreateTable.ExecuteScalar();
            m_Conn.Close();

            return count;
        }


        //将录入的指纹信息插入到数据库
        public void InsertFPInfo2Table(string tableName, string userName, string userAuthority, string userFPData)
        {
            SQLiteConnection m_Conn = SQLiteUtils.GetConnection(Common.DatabaseName);
            string sql = "INSERT INTO " + tableName + "(UserName, UserAuthority , UserFPFeatureData) VALUES (" + "'" + userName + "'," + "'" + userAuthority + "'," + "'" + userFPData + "')";
            SQLiteCommand cmdCreateTable = new SQLiteCommand(sql, m_Conn);
            cmdCreateTable.ExecuteNonQuery();
            m_Conn.Close();
        }

        public void InsertFPRecord2Table(string tableName, string id, string userName, string userAuthority, string Login_Begin_Time)
        {
            SQLiteConnection m_Conn = SQLiteUtils.GetConnection(Common.DatabaseName);
            string sql = "INSERT INTO " + tableName + "(ID, Login_Name , Login_Authority , Login_Begin_Time) VALUES (" + "'" + id + "'," + "'" + userName + "'," + "'" + userAuthority + "'," + "'" + Login_Begin_Time + "')";
            SQLiteCommand cmdCreateTable = new SQLiteCommand(sql, m_Conn);
            cmdCreateTable.ExecuteNonQuery();
            m_Conn.Close();
        }

        public void UpdataFPRecordQuit2Table(string tableName, string id, string Login_End_Time)
        {
            SQLiteConnection m_Conn = SQLiteUtils.GetConnection(Common.DatabaseName);
            string sql = "UPDATE " + tableName + " SET " + "(Login_End_Time) =" + "'" + Login_End_Time + "'" + "where ID = " + "'" + id + "'";
            SQLiteCommand cmdCreateTable = new SQLiteCommand(sql, m_Conn);
            cmdCreateTable.ExecuteNonQuery();
            m_Conn.Close();
        }

        //将录入的指纹信息插入到数据库
        public void InsertFPInfo2TableEx(string tableName, string userName, string userFPData)
        {
            SQLiteConnection m_Conn = SQLiteUtils.GetConnection(Common.DatabaseName);
            string sql = "INSERT INTO " + tableName + "(UserName , UserFPFeatureData) VALUES (" + "'" + userName + "'," + "'" + userFPData + "')";
            SQLiteCommand cmdCreateTable = new SQLiteCommand(sql, m_Conn);
            cmdCreateTable.ExecuteNonQuery();
            m_Conn.Close();
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string userName)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from UserFPInfo ");
            strSql.Append(" where UserName=@UserName ");
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@UserName", DbType.String,8)};
            parameters[0].Value = userName;

            int rows = SQLiteUtils.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //根据时间段查询是否存在于数据库中
        public DataSet InquirySpecifyTime2Table(string startTime, string endTime, string tableName, string TypeName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM " + tableName + " where " + TypeName + " >=" + "'" + startTime + "'" + " and " + TypeName + "<=" + "'" + endTime + "'");

            return SQLiteUtils.Query(strSql.ToString());
        }

    }
}
